﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Configuration;
using System.Collections;

namespace TypedROBIN.Common
{
    public class DaoHelper
    {
        private string m_FactoryName = null;
        private string m_ConnectionString = null;
        private DbProviderFactory m_Factory = null;

        public DaoHelper(string factoryName)
        {
            m_FactoryName = factoryName;
            m_Factory = DbProviderFactories.GetFactory(m_FactoryName);
            ConnectionStringSettingsCollection settings = ConfigurationManager.ConnectionStrings;
            if (settings != null)
            {
                foreach (ConnectionStringSettings cs in settings)
                {
                    if (cs.ProviderName == m_FactoryName)
                        m_ConnectionString = cs.ConnectionString;
                    break;
                }
            }
        }
        
        public DbConnection OpenConnection()
        {
            DbConnection cnn = m_Factory.CreateConnection();
            cnn.ConnectionString = m_ConnectionString;
            cnn.Open();
            return cnn;
        }

        public void CloseConnection(DbConnection connection)
        {
            connection.Close();
            connection.Dispose();
        }

        public DbParameter ConvertToParameter(string name, object value, DbType dbType = DbType.String)
        {
            DbParameter parameter = m_Factory.CreateParameter();
            parameter.ParameterName = name;
            parameter.DbType = dbType;
            parameter.Value = value;
            return parameter;
        }

        public int ExecuteNonQuery(string commandText, DbParameter[] parameters = null)
        {
            int effected = 0;
            using (DbConnection connection = OpenConnection())
            {
                using (DbTransaction transaction = connection.BeginTransaction())
                {
                    using (DbCommand command = m_Factory.CreateCommand())
                    {
                        command.Connection = connection;
                        command.CommandText = commandText;
                        if (parameters != null && parameters.Length > 0)
                        {
                            command.Parameters.AddRange(parameters);
                        }
                        effected = command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                CloseConnection(connection);
            }
            return effected;
        }

        public Hashtable[] ExecuteQuery(string commandText)
        {
            ArrayList al = null;
            using (DbConnection connection = OpenConnection())
            {
                using (DbCommand command = m_Factory.CreateCommand())
                {
                    command.Connection = connection;
                    command.CommandText = commandText;
                    using (DbDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            al = new ArrayList(reader.RecordsAffected);
                            while (reader.Read())
                            {
                                Hashtable ht = new Hashtable(reader.FieldCount);
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    string fieldName = reader.GetName(i);
                                    ht.Add(fieldName, reader.GetValue(i));
                                }
                                al.Add(ht);
                            }
                        }
                        else
                        {
                            al = new ArrayList(0);
                        }
                        reader.Close();
                    }
                }
                CloseConnection(connection);
            }
            return al.ToArray(typeof(Hashtable)) as Hashtable[];
        }
    }

    public enum SortOrder
    {
        ASC,
        DESC
    }

    public class Sort
    {
        public string FieldName;
        public SortOrder Order;
        public Sort(string fieldName, SortOrder order)
        {
            this.FieldName = fieldName;
            this.Order = order;
        }
    }
}
